<?php class MEndroit extends CI_Model {
	
	 function __construct()
	    {
		parent::__construct();
	    }

	function getAllRegion() {
		$data = array();
		$Q = $this->db->get('region');
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	
	function getAllCommune($id_region) {
		$data = array();
		$sql = "SELECT * FROM commune where id_region = '$id_region'";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllCommuneJson($id_region) {
		$json = array();
		$data = array();
		$sql = "SELECT * FROM commune where id_region = '$id_region'";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$json[$row['id_commune']][] = $row['nomcommune'];
			}
			$json = json_encode($json);
			
		}
		$Q->free_result();
		header('Content-Type: application/json');
		echo $json;
	}
	
	function getAllFokontany($id_region, $id_commune) {
		$data = array();
		$sql = "SELECT * FROM fokontany where id_region = '$id_region' and id_commune='$id_commune'";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllFokontanyJson($id_region, $id_commune) {
		$json = array();
		$data = array();
		//$sql = "SELECT * FROM fokontany where id_region = '$id_region' and id_commune='$id_commune'";
		$sql = "SELECT * FROM fokontany where id_region = '$id_commune' and id_commune='$id_region'";		
		//echo $sql;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$json[$row['id_fokontany']][] = $row['nom_fokontany'];
			}
			$json = json_encode($json);
			
		}
		$Q->free_result();
		header('Content-Type: application/json');
		echo $json;
	}


	function getAllAuditeur() {
		$data = array();
		$Q = $this->db->get('auditeur');
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllDetenteur() {
		$data = array();
		$Q = $this->db->get('detenteur');
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllEtablissement() {
		$data = array();
		$Q = $this->db->get('etablissement');
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function insertRegion($data){
		return $this->db->insert('region', $data);
	}

	function insertCommune($data){
		return $this->db->insert('commune', $data);
	}

	function insertFokontany($data){
		return $this->db->insert('fokontany', $data);
	}
	
	function insertEtablissement($data){
		return $this->db->insert('etablissement', $data);
	}

	function verifEtablissement ($nometablissement, $institution, $adresse){
		$nometablissement = mysql_real_escape_string($nometablissement);
		$institution = mysql_real_escape_string($institution);
		$adresse = mysql_real_escape_string($adresse);
		$sql = "SELECT id_etablissement FROM etablissement where nom_etablissement = '$nometablissement' and institution='$institution' and adresse_etablissement='$adresse'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_etablissement'];
			}
		}
		$Q->free_result();
		return $i;
	}

	function insertDetenteur($data){
		return $this->db->insert('detenteur', $data);
	}

	function insertAuditeur($data){
		return $this->db->insert('auditeur', $data);
	}

	function verifDetenteur ($nom){
		$sql = "SELECT id_detenteur FROM detenteur where nom = '$nom'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_detenteur'];
			}
		}
		$Q->free_result();
		return $i;
	}

	

	function verifAuditeur($auditeur){
		$sql = "SELECT id_auditeur FROM auditeur where nom_auditeur = '$auditeur' ";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_auditeur'];
			}
		}
		$Q->free_result();
		return $i;
	}
	function verifRegion ($nom_region){
		$sql = "SELECT id_region FROM region where nomregion = '$nom_region'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_region'];
			}
		}
		$Q->free_result();
		return $i;
	}

	function verifCommune ($id_region , $nomcommune){
		$sql = "SELECT id_commune FROM commune where id_region = '$id_region' and nomcommune='$nomcommune'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_commune'];
			}
		}
		$Q->free_result();
		return $i;
	}

	function verifFokontany ($id_region , $id_commune, $nomfokontany){
		$sql = "SELECT id_fokontany FROM fokontany where id_region = '$id_region' and id_commune='$id_commune' and nom_fokontany='$nomfokontany'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_fokontany'];
			}
		}
		$Q->free_result();
		return $i;
	}

	
	public function fetch_region($limit, $start) {
		$this->db->limit($limit, $start);
		$query = $this->db->get("region");
		if ($query->num_rows() > 0) {
		    foreach ($query->result() as $row) {
			$data[] = $row;
		    }
		    return $data;
		}
		return false;
	}
	
	public function countregion() {
        	return $this->db->count_all("region");
    	}
	
	public function maxidcommune($id_region) {
        	$sql = "SELECT max(id_commune) as max FROM commune where id_region = '$id_region'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['max'];
			}
		}
		$Q->free_result();
		return $i;
    	}

	public function maxidfokontany($id_region, $id_commune) {
        	$sql = "SELECT max(id_fokontany) as max FROM fokontany where id_region = '$id_region' and id_commune='$id_commune'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['max'];
			}
		}
		$Q->free_result();
		return $i;
    	}

	public function maxidregion() {
        	$sql = "SELECT max(id_region) as max FROM region";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['max'];
			}
		}
		$Q->free_result();
		return $i;
    }

	

	public function gettype() {
        	$sql = "SELECT distinct(type) as typedis FROM materiel";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		return $data;
		}
    } 
}
?>